﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace Maticsoft.DAL
{
	/// <summary>
	/// 数据访问类:Members
	/// </summary>
	public partial class Members
	{
        public DataSet GetMemberByUserNamePassword(string userName, string password)
        {
            string strSql = "select Members.UserId,Members.UserName,Members.GroupId,MemberGroup.GroupName from Members join MemberGroup on Members.GroupId=MemberGroup.GroupId where UserName=@UserName and Password=@Password and (Members.GroupId=1 or Members.GroupId=2) and Members.IsActive=1";
            SqlParameter[] para ={
                                new SqlParameter("@UserName",SqlDbType.NVarChar,50),
                                new SqlParameter("@Password",SqlDbType.NVarChar,50)
                                };
            para[0].Value = userName;
            para[1].Value = password;

            return DbHelperSQL.Query(strSql, para);
        }

        public Maticsoft.Model.Members GetModel(string userName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select  top 1  UserId,UserName,Password,GroupId,LevelId,NickName,RealName,Email,Mobile,Phone,Birthday,Sex,LocationId,PostCode,Address,Question,Answer,QQ,MSN,Weixin,IdentityType,IdentityCard,Credits,Pic,Status,CreateTime,UpdateTime,LastLoginTime,IsActive,IdentityCardPic1,IdentityCardPic2 from Members ");
            strSql.Append(" where UserName=@UserName");
            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = userName;

            Maticsoft.Model.Members model = new Maticsoft.Model.Members();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public Maticsoft.Model.Members GetModel(string userName,string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select  top 1 [UserId]
                      ,[UserName]
                      ,[Password]
                      ,[GroupId]
                      ,[LevelId]
                      ,[NickName]
                      ,[RealName]
                      ,[Email]
                      ,[Mobile]
                      ,[Phone]
                      ,[Birthday]
                      ,[Sex]
                      ,[LocationId]
                      ,[PostCode]
                      ,[Address]
                      ,[Question]
                      ,[Answer]
                      ,[QQ]
                      ,[MSN]
                      ,[Weixin]
                      ,[IdentityType]
                      ,[IdentityCard]
                      ,[Credits]
                      ,[Pic]
                      ,[Status]
                      ,[CreateTime]
                      ,[UpdateTime]
                      ,[LastLoginTime]
                      ,[IsActive]
                      ,[IdentityCardPic1]
                      ,[IdentityCardPic2] from Members ");
            strSql.Append(" where UserName=@UserName and Password=@Password");
            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                    new SqlParameter("@Password", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = userName;
            parameters[1].Value = password;

            Maticsoft.Model.Members model = new Maticsoft.Model.Members();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public int DeleteByIds(string ids)
        {
            string strSql = "Update  Members set IsActive=0 where UserId in(" + ids + ")";

            return DbHelperSQL.ExecuteSql(strSql);
        }

        public int UpdateStatusByIds(string ids, int stat)
        {
            string strSql = "update Members set Status=@Status where UserId in(" + ids + ")";
            SqlParameter[] para ={
                                new SqlParameter("@Status",SqlDbType.Int)
                                };
            para[0].Value = stat;
            return DbHelperSQL.ExecuteSql(strSql, para);
        }
	}
}

